<?php
App::import('Vendor', 'linkbot');
App::import('Vendor', 'email_message');
App::import('Vendor', 'image');

class Ajax extends AppModel{
	var $name              = 'Ajax';
	var $useTable          = 'markers';
	var $regions           = 'regions';
	var $regionsCoordTable = 'regions_coord';
	var $destructionTable  = 'destruction';
	var $userProp          = 'user_properties';
	var $usersInfoTable    = 'users_information';
	var $chatTable         = 'chat';
	var $gameTable         = 'game_info';
	var $gameLocatonsTable = 'game_locations';
	
	function getGameGroup($game_id){
		$games = array();
		$result = $this->query("SELECT u.`first_name`, u.`last_name` 
			FROM `{$this->gameTable}` as g,`{$this->usersInfoTable}` as u 
			WHERE g.`user_id` = u.`user_id` AND
				g.`game_id` = {$game_id}");
		foreach($result as $res){
			$games[] = $res['u'];
		}
		return $games;
	}
	
	function insertChatMessage($msg,$id,$nickname){
		$this->query("INSERT INTO `{$this->chatTable}`(`message`,`nickname`,`user_id`) 
			VALUES('".$this->escape($msg)."','".$this->escape($nickname)."',{$id})");
		
		$result = $this->query("SELECT LAST_INSERT_ID() AS `id`");
		foreach($result as $res){
			$chat_id = $res[0]['id'];
		}
		return $chat_id;
	}

	function readLast50Chats($chat_id,$nickname,$game_id){
		$query = "SELECT DISTINCT c.*
				FROM `{$this->chatTable}` as c
				WHERE c.`chat_id`>{$chat_id} AND
					c.`nickname`!='{$nickname}' AND
					c.`user_id` IN (SELECT DISTINCT `user_id`
									FROM `{$this->gameTable}`
									WHERE `game_id` = {$game_id})
				ORDER BY c.`chat_id` ASC 
				LIMIT 0,50";
		$result = $this->query($query);
		$chats = array();
		foreach($result as $res) {
			$chats[] = $res['c'];
		}
		return $chats;
	}
	
	function getDataTypes($what,$table = "markers"){
		$query = "SHOW COLUMNS FROM `{$table}`
				WHERE `field` = '{$what}'";
		$result = $this->query($query);
		foreach($result as $res){
			$cols = $res['COLUMNS']['Type'];
		}
		$cols = str_replace("enum(","",$cols);
		$cols = str_replace(")","",$cols);
		$cols = str_replace("'","",$cols);
		return explode(",", $cols);
	}
	
	function insertRegions($lat_start,$lat_end, $long_start, $long_end, $game_id) {
		$result = $this->query("SELECT `game_location_id` FROM `{$this->gameTable}` 
							WHERE `game_id` = {$game_id}");
		$location_id = 0;
		foreach($result as $res) {
			$location_id = $res[$this->gameTable]['game_location_id'];
 		}
		$query = "INSERT INTO `{$this->regionsCoordTable}`(`start_lat`,`start_long`,`end_lat`,`end_long`,`game_location_id`)
				VALUES({$lat_start},{$long_start},{$lat_end},{$long_end},{$location_id})";
		$result = $this->query($query);
	}
	
	function searchProperty($lat,$long,$game_id){
		$query = "SELECT m.*, u.`first_name`, u.`user_id`, u.`last_name`, p.`img_url`, 
						p.`property_id`, p.`price_buy`, p.`forsale`, r.`region_worth`,
						p.`property_name`
				FROM `{$this->useTable}` as `m`, `{$this->userProp}` as `p`, 
						{$this->usersInfoTable} as `u`, {$this->regions} as `r`,
						{$this->gameTable} as `g`
				WHERE (ABS(m.`pos_lat`-{$lat})<=0.00025  
					AND ABS(m.`pos_long`-{$long})<=0.00025)
					AND m.`marker_id` = p.`marker_id` 
					AND p.`user_id`   = u.`user_id`
					AND r.`region_id` = m.`region_id`
					AND r.`game_id` = g.`game_id`
					AND m.`valid`=1
					AND g.`user_id` = u.`user_id` 
					AND g.`game_id` = {$game_id}				
				LIMIT 1";
		$result = $this->query($query);
		if(count($result)>0){
			return 1;	
		} else {
		 	return 0;		
		}
	}
	
	function getRegion($lat,$long,$game_id){
		$query = "SELECT r.*
				FROM `{$this->regions}` as r,
					`{$this->regionsCoordTable}` as rc,
					`{$this->gameTable}` as g 
				WHERE (rc.`start_lat`<={$lat} AND rc.`end_lat`>={$lat}) 
					AND (rc.`start_long`<={$long} AND rc.`end_lat`>={$long}) 
					AND g.`game_location_id` = rc.`game_location_id`
					AND r.`game_id` = g.`game_id`
					AND r.`region_coord_id` = rc.`region_coord_id` 
					AND g.`game_id` = {$game_id}";
		$result = $this->query($query);
		$region = array();
		if(count($result)>0){
			foreach($result as $res){
				$region = $res['r'];
			}
		}
		return $region;		
	}
	
	function buyProperty($marker_id, $user_id, $prop_value) {
		//get old owner id
		$result = $this->query("SELECT `user_id` 
					FROM `{$this->userProp}` 
					WHERE `marker_id`={$marker_id}");
		if(count($result)>0){
			foreach($result as $res){
				$old_owner = $res[$this->userProp]['user_id'];
			}
		}
		$result = $this->query("UPDATE `{$this->userProp}` 
					SET `user_id`={$user_id} 
					WHERE `marker_id`={$marker_id}");
		//reduce the user's money
		$result = $this->query("UPDATE `{$this->usersInfoTable}` 
				SET `money`= `money` - {$prop_value} 
				WHERE `user_id`={$user_id}");
		//give the money to the old owner
		$result = $this->query("UPDATE `{$this->usersInfoTable}` 
				SET `money`= `money` + {$prop_value} 
				WHERE `user_id`={$old_owner}");
	}
	
	function getDestruction($destruction_name){
		$queryStr = "SELECT *
					FROM `{$this->destructionTable}` 
					WHERE LOWER(`destruction_name`) = LOWER('{$destruction_name}')";
		$result = $this->query($queryStr);
		$destructions = array();
		foreach($result as $res){
			$destructions = $res[$this->destructionTable];	
		}
		return $destructions;
	}
}